﻿using System.Linq;
using System.Data.Objects;
using System.Data.Linq;
        
namespace NorthwindEntities
{
    public class EmployesWithTeritories: Employee
    {
        private EntitySet<Territory> territories;
        
        public EmployesWithTeritories(int empId)
        {
            this.territories = GetTeritories(empId);
        }

        private void MapProperties(Employee currentEmploye)
        {
            this.EmployeeID = currentEmploye.EmployeeID;
            this.FirstName = currentEmploye.FirstName;
            this.LastName = currentEmploye.LastName;
            this.HireDate = currentEmploye.HireDate;
            this.Title = currentEmploye.Title;
            this.TitleOfCourtesy = currentEmploye.TitleOfCourtesy;
            //to lazy to implement and probably there is a better way to do it
        }

        private EntitySet<Territory> GetTeritories(int empId)
        {
            NorthwindEntities context = new NorthwindEntities();

            Employee currentEmploye =  GetCurrentEmployee(empId, context);
            MapProperties(currentEmploye);
            ObjectResult<Territory> teritories = GetTerritoriesFromDataBase(context);

            EntitySet<Territory> output = new EntitySet<Territory>();
            foreach (var item in teritories) 
            {
                output.Add(item);
            }

            return output;
        }
  
        private ObjectResult<Territory> GetTerritoriesFromDataBase(NorthwindEntities context)
        {
            string nativeQuerry = "Select ter.TerritoryID, " +
                                  " ter.TerritoryDescription, " +
                                  " ter.RegionID " +
                                  " From Employees emp join EmployeeTerritories empTeritory " +
                                  " on emp.EmployeeID = empTeritory.EmployeeID " +
                                  " join Territories ter " +
                                  " on empTeritory.TerritoryID = ter.TerritoryID" +
                                  " Where emp.EmployeeID = {0}";
            object[] parameters = { this.EmployeeID };
            ObjectResult<Territory> teritories = context.ExecuteStoreQuery<Territory>(nativeQuerry, parameters);
            return teritories;
        }
  
        private Employee GetCurrentEmployee(int empId, NorthwindEntities context)
        {
            string selectEmploye = "Select * from Employees where EmployeeID = {0}";
            object[] parameters = { empId };
            ObjectResult<Employee> employee = context.ExecuteStoreQuery<Employee>(selectEmploye, parameters);

            Employee currentEmployee = employee.ElementAt<Employee>(0);

            return currentEmployee;
        }

        public EntitySet<Territory> Teritories
        {
            get
            {
                return territories;
            }
            set
            {
                territories = value;
            }
        }
    }
}
